package org.nokatag.dbgrid;

import java.io.BufferedReader;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.io.Reader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.nokatag.password.AESPlus;
import org.nokatag.system.BugInfoWrint;
import org.nokatag.system.Concest;
import org.nokatag.system.Function;
import org.nokatag.system.PreparedParemSet;
import org.nokatag.system.RunUserFunction;


/**
 * 数据服务接口
 * @author rebin
 */
public class NDBGridData{
	
	public NDBGridDataItem DataQuery(NDBGridDataQueryItem dq,Connection con){
		try{
			//----------------------加载查询条件-----------------------------------------------------------
			String sql=dq.getSql();
			if(dq.getWsql()!=null)
				sql="select   * from ("+sql+")  temptable_"+System.currentTimeMillis()+" where 1=1 "+ dq.getWsql();
			else
				sql="select   * from ("+sql+")  temptable_"+System.currentTimeMillis()+" where 1=1 ";
			//----------------------总记录数--------------------------------------------------------------
			PreparedStatement pa = null;
			ResultSet rsf = null;
			Long lastRow = 0L;
			try{
				pa= con.prepareStatement(" select count(1) as conet from  ("+ sql + ")  temptable_" + System.nanoTime(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
				for(CellParItem c:dq.getParList()){
					setPares(pa,c.getIndex(),c.getValue(),c.getType());
				}
				rsf=pa.executeQuery();
				rsf.next();
				lastRow = rsf.getLong("conet");// 一共有多少条记录
			}catch(Exception se){
				
			}finally{
				if(null!=pa){
					try{pa.close();}catch(Exception se){}
				}
				if(null!=rsf){
					try{rsf.close();}catch(Exception se){}
				}
			}
			//----------------------获取数据--------------------------------------------------------------
			Long showPage =dq.getShowPage();
			Long pageSize =dq.getPageSize()==null?(lastRow<1?1:lastRow):dq.getPageSize();
			Long pageConut = (lastRow % pageSize == 0) ? (lastRow / pageSize): (lastRow / pageSize + 1);// 总的页数
			if (showPage > pageConut)
				showPage = pageConut;
			if (showPage < 1)
				showPage = 1L;
			Long posion = (showPage - 1) * pageSize+1;// 游标位置
			PreparedStatement pa2= null;
			ResultSet rss =  null;
			List<Map<String,String>> rows = null;
			List<String> cellnames=null;
			try{
				pa2= con.prepareStatement(sqlpx(dq,sql),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
				for(CellParItem c:dq.getParList()){
					setPares(pa2,c.getIndex(),c.getValue(),c.getType());
				}
				rss = pa2.executeQuery();
				rss.absolute(posion.intValue());// 设置游标位置
				ResultSetMetaData rsmd = rss.getMetaData();// 取得列名
				rows = new ArrayList<Map<String,String>>();
				cellnames=new ArrayList<String>();//列名 
				for (int i = 1; i <= pageSize; i++) {
					Map<String,String> row=new HashMap<String,String>();
					Map<String, Object> map=new HashMap<String, Object>();//列名=值
					for(int a=1;a<=rsmd.getColumnCount();a++){
						cellnames.add(rsmd.getColumnName(a));//列名 
						String cellvalue= frmotDate(rsmd.getColumnType(a),rss,a);
						map.put(rsmd.getColumnName(a), cellvalue);
					}
					for(int a=0;a<cellnames.size();a++){
						String value=formatValue(map.get(cellnames.get(a)),dq.getFormatcell(),cellnames.get(a),dq.getRequest(),map);
						row.put(cellnames.get(a), value);//列名,值
					}
					rows.add(row);
					if (!rss.next())
						break;
				}
			}catch(Exception se){
				
			}finally{
				if(null!=rss){
					try{rss.close();}catch(Exception se){}
				}
				if(null!=pa2){
					try{pa2.close();}catch(Exception se){}
				}
			}
			//---------------------汇总列-----------------------------------------------------------------
			String sumcells = dq.getSumcells();
			Map<String,String> sumCells= new HashMap<String,String>();//汇总列
			if(null!=sumcells && !"-1".equals(sumcells)){
				String[] cidex = sumcells.split(",");
				String sum_cell = "*";
				for(String idex:cidex){
					try{
						int cid = Integer.parseInt(idex);
						String cellName = cellnames.get(cid);//汇总的列名
						if(null!=cellName && cellName.trim().length()>0){
							sum_cell+=",sum("+cellName+") as "+cellName;
						}
					}catch(Exception ex){}
				}
				if(!"*".equals(sum_cell)){
					sum_cell=sum_cell.substring(2);
				}
				String sumSQL = "select "+sum_cell+" from ("+dq.getSumsql()+")  temptable_"+System.currentTimeMillis();
				PreparedStatement pa_sum = null;
				ResultSet rs_sum = null;
				try{
					pa_sum= con.prepareStatement(sumSQL,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
					for(CellParItem c:dq.getParList()){
						setPares(pa_sum,c.getIndex(),c.getValue(),c.getType());
					}
					rs_sum=pa_sum.executeQuery();
					ResultSetMetaData rsmd_sum = rs_sum.getMetaData();// 取得列名
					if(rs_sum.next()){
						Map<String, Object> map_sum=new HashMap<String, Object>();//列名=值
						List<String> cellnames_sum=new ArrayList<String>();//列名 
						for(int a=1;a<=rsmd_sum.getColumnCount();a++){
							cellnames_sum.add(rsmd_sum.getColumnName(a));//列名 
							map_sum.put(rsmd_sum.getColumnName(a), rs_sum.getString(a));
						}
						for(int a=0;a<cellnames_sum.size();a++){
							String value=formatValue(map_sum.get(cellnames_sum.get(a)),dq.getSumformatcell(),cellnames_sum.get(a),dq.getRequest(),map_sum);
							sumCells.put(cellnames_sum.get(a), value);
						}
					}
				}catch(Exception ex){
				}finally{
					if(null!=pa_sum){
						try{pa_sum.close();}catch(Exception se){}
					}
					if(null!=rs_sum){
						try{rs_sum.close();}catch(Exception se){}
					}
				}
			}
			//--------------------------------------------------------------------------------------------
			NDBGridDataItem nd = new NDBGridDataItem(pageConut, showPage, posion+1, (posion+rows.size()),lastRow, Long.valueOf(rows.size()),rows,cellnames);
			nd.setSumCells(sumCells);
			return nd;
		}catch(Exception se){
			BugInfoWrint.Print(se);
		}
		return null;
	}
	//--------------sql 排序--------------------------------------
	protected String sqlpx(NDBGridDataQueryItem dq,String sql){
	    String pagedescorasc       =dq.getPagedescorasc();//排序方式
	    String pagedescorname      = dq.getPagedescorname();//排序列的名字
	    //------------初始参数检测---------------------------------------------------
	    if(Function.isStringNotNull(pagedescorasc)){//参数不合法
	    	if(!pagedescorasc.trim().equalsIgnoreCase("asc") && !pagedescorasc.trim().equalsIgnoreCase("desc")){//排序方式只是asc或desc
		    	return sql;
		    }
	    }
	    if(Function.isStringNotNull(pagedescorname) && Function.isStringNotNull(pagedescorasc) ){
	    	if(sql.indexOf("$[page]")!=-1){
	    		sql=sql.replace("$[page]", " order by "+pagedescorname+" "+pagedescorasc+"$[page]");
	    	}else{
	    		sql+="  order by "+pagedescorname+" "+pagedescorasc;
	    	}
	    }
	    return sql;
	}
	//-----------------格式化值----------------------------------
	protected String formatValue(Object value,String formatcell,String cellname,HttpServletRequest request,Map<String, Object> map){
		try{
		if(value==null)
			return null;
		if(formatcell!=null && formatcell.trim().length()>0){
			formatcell=AESPlus.decrypt(formatcell,Concest.SQLKEY);
			for(String fun:formatcell.split("\\|")){
				fun=fun.trim();
				String cn = fun.substring(fun.indexOf(" as ")+" as ".length(),fun.length());
				if(cn.trim().equals(cellname.trim())){
					fun=fun.split(" as ")[0];
					value=RunUserFunction.TagFunction(fun, request, map);
				}
			}
		}
		}catch(Exception se){
			se.printStackTrace();
		}
		if(value==null)
			return null;
		return value.toString();
	}
	//---------------------------------内置日期格式化------------------------------------
	protected String frmotDate(Date d){
		String ds = null;
		try{
			SimpleDateFormat smf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
			ds = smf.format(d);
		}catch(Exception se){}
		return ds;
	}
	//-------------------------------格式化数据--------------------------------------------
	protected String frmotDate(int t,ResultSet rss,int a) throws SQLException{
		String ds = "";
		switch(t){
		case  java.sql.Types.TIMESTAMP:
			try{
				SimpleDateFormat smf = new SimpleDateFormat(Concest.DEFAULT_DATETIME);
				ds = smf.format(rss.getTimestamp(a));
			}catch(Exception se){}
			break;
		case  java.sql.Types.DATE:
			try{
				SimpleDateFormat smf = new SimpleDateFormat(Concest.DEFAULT_DATE);
				ds = smf.format(rss.getDate(a));
			}catch(Exception se){}
			break;
		case  java.sql.Types.TIME:
			try{
				SimpleDateFormat smf = new SimpleDateFormat(Concest.DEFAULT_TIME);
				ds = smf.format(rss.getTime(a));
			}catch(Exception se){}
			break;
		case java.sql.Types.CLOB:
			ds =toCharArray(rss.getCharacterStream(a));
			break;
		case java.sql.Types.BLOB:
			ds =toByteArray(rss.getBinaryStream(a));
			break;
		case java.sql.Types.INTEGER:
			ds = String.valueOf(rss.getInt(a));
			break;
		case java.sql.Types.BIGINT:
			ds = String.valueOf(rss.getLong(a));
			break;
		case java.sql.Types.BINARY:
			ds =toByteArray(rss.getBinaryStream(a));
			break;
		case java.sql.Types.VARBINARY:
			ds =toByteArray(rss.getBinaryStream(a));
			break;
		case java.sql.Types.LONGVARBINARY:
			ds =toByteArray(rss.getBinaryStream(a));
			break;
		case java.sql.Types.TINYINT:
			ds = String.valueOf(rss.getShort(a));
			break;
		case java.sql.Types.SMALLINT:
			ds = String.valueOf(rss.getShort(a));
			break;
		case java.sql.Types.DOUBLE:
			ds = String.valueOf(rss.getDouble(a));
			break;
		case java.sql.Types.REAL:
			ds = String.valueOf(rss.getFloat(a));
			break;
		case java.sql.Types.FLOAT:
			ds = String.valueOf(rss.getFloat(a));
			break;
		default:
			ds = rss.getString(a);
			break;
		}
		return ds;
	}
	private  String toByteArray(InputStream input){
		ByteArrayOutputStream output = new ByteArrayOutputStream();
		try{
		    byte[] buffer = new byte[1024];
		    int n = 0;
		    while (-1 != (n = input.read(buffer))) {
		        output.write(buffer, 0, n);
		    }
		}catch(Exception se){
			BugInfoWrint.Print(se);
		}
		return new String(output.toByteArray());
	}
	private  String toCharArray(Reader r){
		BufferedReader bufread  = null;
		StringBuffer sb = new StringBuffer();
		try{
			bufread = new BufferedReader(r);
			String read;
			while ((read = bufread.readLine()) != null) {
				sb.append(read);
			}
		}catch(Exception se){
			BugInfoWrint.Print(se);
		}finally{
			if(null!=bufread){
				try{bufread.close();}catch(Exception e){}
			}
			if(null!=r){
				try{r.close();}catch(Exception e){}
			}
		}
		return sb.toString();
	}
	//-----------------------------设置参数--------------------------------------------------------------
	protected void setPares(PreparedStatement pa,Integer indx,Object o,int types) throws SQLException{
		PreparedParemSet pset = new PreparedParemSet();
		pset.setPre(pa,indx,o,types);
	}
	
}
